package org.sjlt.marc.dataservices;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.hibernate.annotations.common.util.StringHelper;
import org.json.simple.JSONArray;
import org.json.simple.JSONObject;
import org.sjlt.marc.common.BigHelpDBConnection;
import org.sjlt.marc.common.Queries;
import org.sjlt.marc.resources.AliasPair;

public class MeetingSigninService extends HttpServlet 
{
	private static final long serialVersionUID = 1L;
       
    public MeetingSigninService() 
    {
        super();
    }
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
    {
		doPost(request,response);
	}
    
	@SuppressWarnings("unchecked")
	@Override
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException 
	{
		if(StringHelper.isNotEmpty(request.getParameter("RequestType")))
		{
			response.setContentType("application/json");
			PrintWriter pw = response.getWriter();
			String requestType = request.getParameter("RequestType");
			BigHelpDBConnection bhdbcon = new BigHelpDBConnection("sjlt_marc");
			
			if("getActiveList".equals(requestType))
			{
				Vector<AliasPair> activeUserList = new Vector<AliasPair>();
				Vector<AliasPair> activeAliasList = new Vector<AliasPair>();
				Vector<AliasPair> activeAdminList = new Vector<AliasPair>();
				try
				{
					JSONObject output = new JSONObject();
					JSONArray inUsers = new JSONArray();
					Connection conn = bhdbcon.getConnection();
					String MeetingID = request.getParameter("MeetingID");
					PreparedStatement ps = conn.prepareStatement(Queries.SQL_GATHER_ACTIVE_ALIASES);
					PreparedStatement ps2 = conn.prepareStatement(Queries.SQL_GATHER_ACTIVE_USERS);
					PreparedStatement ps3 = conn.prepareStatement(Queries.SQL_GATHER_ACTIVE_ADMINS);
					PreparedStatement ps4 = conn.prepareStatement(Queries.SQL_GATHER_SIGNED_IN_USERS);
					
					try
					{
						ps4.setString(1, MeetingID);
						ResultSet rs = ps.executeQuery();
						ResultSet rs2 = ps2.executeQuery();
						ResultSet rs3 = ps3.executeQuery();
						ResultSet rs4 = ps4.executeQuery();
						try
						{
							while(rs.next())
							{
								String aUserID = rs.getString("UserID");
								String aAlias = rs.getString("Alias");
								String aPref_Name = rs.getString("Pref_Name");
								String aLName = rs.getString("LName");
								String aPicture = rs.getString("Picture");
								
								activeAliasList.add(new AliasPair(aUserID, aAlias, aPref_Name, aLName, aPicture));
							}
							while(rs2.next())
							{
								String uUserID = rs2.getString("UserID");
								String uAlias = rs2.getString("Alias");
								String uPref_Name = rs2.getString("Pref_Name");
								String uLName = rs2.getString("LName");
								String uPicture = rs2.getString("Picture");
								
								activeUserList.add(new AliasPair(uUserID, uAlias, uPref_Name, uLName, uPicture));
							}
							while(rs3.next())
							{
								String mUserID = rs3.getString("UserID");
								String mAlias = rs3.getString("Alias");
								String mPref_Name = rs3.getString("Pref_Name");
								String mLName = rs3.getString("LName");
								String mPicture = rs3.getString("Picture");
								
								activeAdminList.add(new AliasPair(mUserID, mAlias, mPref_Name, mLName, mPicture));
							}
							
							while(rs4.next())
							{
								inUsers.add(rs4.getString("UserID"));
							}

							output.put("ActiveAliases", activeAliasList);
							output.put("ActiveUsers", activeUserList);
							output.put("ActiveAdmins", activeAdminList);
							output.put("inUsers", inUsers);
							pw.println(output.toString());
						}
						finally
						{
							rs.close();
							ps2.close();
							ps3.close();
							ps4.close();
						}
					}
					finally
					{
						ps.close();
						ps2.close();
						ps3.close();
						ps4.close();
					}
				}
				catch(SQLException se)
				{
					se.printStackTrace();
				}
				finally
				{
					bhdbcon.closeConnection();
				}
			}
			else if("meetingTypes".equals(requestType))
			{
				try
				{
					JSONArray meetingIds = new JSONArray();
					JSONArray meetingTypes = new JSONArray();
					JSONObject output = new JSONObject();
					Connection conn = bhdbcon.getConnection();
					PreparedStatement ps = conn.prepareStatement(Queries.SQL_GET_MTG_TYPES);
					
					try
					{
							ResultSet rs = ps.executeQuery();
							try
							{
								while(rs.next())
								{
									meetingIds.add(rs.getString("MeetingTypeID"));
									meetingTypes.add(rs.getString("MeetingTypeName"));
								}

								output.put("MeetingTypes", meetingTypes);
								output.put("MeetingTypeIDs", meetingIds);
								pw.println(output.toString());
							}
							finally
							{
								rs.close();
							}
					}
					finally
					{
						ps.close();
					}
				}
				catch(SQLException se)
				{
					se.printStackTrace();
				}
				finally
				{
					bhdbcon.closeConnection();
				}
			}
			else if("startNewMeeting".equals(requestType))
			{
				try 
				{
					String MeetingType = request.getParameter("MeetingType");
					//String StartDate = request.getParameter("StartDate") + " 18:30";
					String UserId = request.getParameter("UserId");
					String AttendantMode = request.getParameter("AttendantMode");
					
					JSONObject output = new JSONObject();
					Connection conn = bhdbcon.getConnection();
					PreparedStatement ps = conn.prepareStatement(Queries.SQL_CREATE_NEW_MEETING);
					PreparedStatement ps2 = conn.prepareStatement(Queries.SQL_GET_CURRENT_MTG_ID);
					
					ps.setString(1,MeetingType);
					//ps.setString(2, StartDate);
					ps.setString(2, UserId);
					ps.setString(3, AttendantMode);
					
					Integer returnVal = ps.executeUpdate();
					if(returnVal > 0)
					{
						ResultSet rs = ps2.executeQuery();
						try
						{
							while(rs.next())
							{
								Integer meetingId = rs.getInt("MeetingID");
								
								if(meetingId > 0)
								{
									output.put("MeetingID", meetingId);
									pw.println(output.toString());
								}
							}
						}
						finally
						{
							rs.close();
							ps.close();
							ps2.close();
						}
					}	
				} 
				catch (SQLException e) 
				{
					e.printStackTrace();
				}
				finally
				{
					bhdbcon.closeConnection();
				}
			}
			else if("CheckForOpenMeetings".equals(requestType))
			{
				try 
				{
					JSONArray meetingIds = new JSONArray();
					JSONArray meetingTypes = new JSONArray();
					JSONArray mtgStarters = new JSONArray();
					JSONArray mtgAttendant = new JSONArray();
					JSONObject output = new JSONObject();
					Connection conn = bhdbcon.getConnection();
					PreparedStatement ps = conn.prepareStatement(Queries.SQL_GET_OPEN_MTGS);

					ResultSet rs = ps.executeQuery();
					try
					{
						while(rs.next())
						{
							meetingIds.add(rs.getInt("MeetingID"));
							meetingTypes.add(rs.getString("MeetingTypeName"));
							mtgStarters.add(rs.getString("StartedBy"));
							mtgAttendant.add(rs.getString("AttendantMode"));
						}
						
						output.put("MeetingIDs", meetingIds);
						output.put("MeetingTypes", meetingTypes);
						output.put("MeetingStarters", mtgStarters);
						output.put("MeetingAttendant", mtgAttendant);
						pw.println(output.toString());
					}
					finally
					{
						rs.close();
						ps.close();
					}
				}
				catch (SQLException e) 
				{
					e.printStackTrace();
				}
				finally
				{
					bhdbcon.closeConnection();
				}
			}
			else if("signUserIn".equals(requestType))
			{
				String MeetingID = request.getParameter("MeetingID");
				String AliasUsed = request.getParameter("AliasUsed");
				
				try 
				{
					Connection conn = bhdbcon.getConnection();
					PreparedStatement ps = conn.prepareStatement(Queries.SQL_SIGN_IN_USER);
					
					ps.setString(1, MeetingID);
					ps.setString(2, AliasUsed);
					
					try
					{
						ps.executeUpdate();
					}
					finally
					{
						ps.close();
					}
					
					JSONArray inUsers = new JSONArray();
					JSONObject output = new JSONObject();
					
					PreparedStatement ps2 = conn.prepareStatement(Queries.SQL_GATHER_SIGNED_IN_USERS);
					
					ps2.setString(1, MeetingID);

					try
					{
						ResultSet rs = ps2.executeQuery();
						while(rs.next())
						{
							inUsers.add(rs.getString("UserID"));
						}
						
						output.put("inUsers", inUsers);
						pw.println(output.toString());
					}
					finally
					{
						ps2.close();
					}
				} 
				catch (SQLException e) 
				{
					e.printStackTrace();
				}
				finally
				{
					bhdbcon.closeConnection();
				}
			}
			else if("closeMeeting".equals(requestType))
			{
				try
				{
					//other functions
					String MeetingID = request.getParameter("MeetingID");
					String UserID = request.getParameter("UserID");
					JSONObject output = new JSONObject();
					
					Connection conn = bhdbcon.getConnection();
					PreparedStatement ps = conn.prepareStatement(Queries.SQL_CLOSE_MEETING);
	
					ps.setString(1, UserID);
					ps.setString(2, MeetingID);
					
					try
					{
						ps.executeUpdate();
					}
					finally
					{
						ps.close();
					}
				}
				catch(Exception e)
				{
					e.printStackTrace();
				}
				finally
				{
					bhdbcon.closeConnection();
				}
			}
			/*else if("requestTypeString".equals(requestType))
			{
				other functions;
			}*/
		}
	}
}
